﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using Citi.Helpers;
using Citi.PTSConsolidation.Domain;
using Citi.PTSConsolidation.Domain.Utils;
using Citi.PTSConsolidation.SchedulerUI.SchedulerWcf;
using Citi.RoutineBase;
using OfficeOpenXml;
using SqlHelper = Citi.PTSConsolidation.Domain.Utils.SqlHelper;

namespace Citi.PTSConsolidation.SchedulerUI
{
    public class ReportGenerationHelper
    {
        private static string PtsConnectionString { get { return ConfigurationManager.AppSettings["PtsConnectionString"]; } }
        private static string PtsReportSaveTempPath { get { return ConfigurationManager.AppSettings["PtsReportSaveTempPath"]; } }
        private static string PtsReportSavePath { get { return ConfigurationManager.AppSettings["PtsReportSavePath"]; } }

        private static string TemplateExcelReport { get { return ConfigurationManager.AppSettings["TemplateExcelReport"]; } }
        private static string TemplateExcelPtsSystemMonthly { get { return ConfigurationManager.AppSettings["TemplateExcelPtsSystemMonthly"]; } }
        public static string TemplateExcelApplicationReport { get { return ConfigurationManager.AppSettings["TemplateExcelApplicationReport"]; } }
        public static string TemplateExcelBZTEApplicationReport { get { return ConfigurationManager.AppSettings["TemplateExcelBZTEApplicationReport"]; } }
        public static string TemplateExcelLagReport { get { return ConfigurationManager.AppSettings["TemplateExcelLAGReport"]; } }

        public const string BookOfWorkLagReportName = "BookOfWorkLAG";
        public const string ApplicationReportName = "ApplicationBookOfWorkLAG";
        public const string BZTEApplicationReportName = "BZTE-ApplicationBookOfWorkLAG";

        static int startDateColumn;

        public static void GenerateClientMonthlyVarianceReportYear(int year, DateTime referenceDate, ref ICurrentAction currentAction, Dictionary<string, object> parameters,
            string yearFriendlyReportName)
        {
            var template = ConfigurationManager.AppSettings["TemplateExcelPtsClientMonthly"];
            var pathReport = Path.Combine(PtsReportSavePath, "Monthly Client Variance");

            currentAction = GenerateClientMonthlyVarianceReportYear(year, referenceDate, currentAction, parameters, yearFriendlyReportName,
                template, pathReport, "BookOfWorkMonthlyClient");
        }

        public static void GenerateClientJustificationMonthlyVarianceReportYear(int year, DateTime referenceDate, ref ICurrentAction currentAction,
            Dictionary<string, object> parameters, string yearFriendlyReportName)
        {
            var path = ConfigurationManager.AppSettings["ClientJustificationMonthlyReportPath"];
            var template = ConfigurationManager.AppSettings["TemplateExcelPtsClientJustificationMonthly"];

            currentAction = GenerateClientMonthlyVarianceReportYear(year, referenceDate, currentAction, parameters, yearFriendlyReportName,
                template, path, "BookOfWorkMonthlyClient");
        }

        private static ICurrentAction GenerateClientMonthlyVarianceReportYear(int year, DateTime referenceDate, ICurrentAction currentAction, Dictionary<string, object> parameters,
            string yearFriendlyReportName, string templateExcelPtsClientMonthly, string pathToSave, string reportName)
        {
            var robotUsername = parameters["RobotUsername"].ToString();
            var robotPassword = parameters["RobotPassword"].ToString();

            startDateColumn = 12;

            var columns = new List<int> { 12, 13, 14, 15, 16, 17 };
            var queryReport = BookOfWork.GetClientDailyVarianceReportQuery(year, referenceDate.Date);

            var filePathSaved = GenerateMonthlyVarianceReportYear(referenceDate, ref currentAction, robotUsername, robotPassword, pathToSave,
                reportName, queryReport, templateExcelPtsClientMonthly, columns, 1, 1000);

            ReportFinishedEmailParameter.SendReportFinishedEmail(parameters, ref currentAction, filePathSaved,
                string.Format("PTS Monthly Client Report ({0})", yearFriendlyReportName), ".xlsx");
            return currentAction;
        }

        public static void GenerateSystemMonthlyVarianceReportYear(int year, DateTime referenceDate, ref ICurrentAction currentAction, Dictionary<string, object> parameters,
            string yearFriendlyReportName)
        {
            var robotUsername = parameters["RobotUsername"].ToString();
            var robotPassword = parameters["RobotPassword"].ToString();

            startDateColumn = 10;

            var columns = new List<int> { 10, 11, 12, 13 };
            var queryReport = BookOfWork.GetSystemDailyVarianceReportQuery(year, referenceDate.Date);

            var pathReport = Path.Combine(PtsReportSavePath, "Monthly System Variance");

            var filePathSaved = GenerateMonthlyVarianceReportYear(referenceDate, ref currentAction, robotUsername, robotPassword, pathReport,
                "BookOfWorkMonthlySystem", queryReport, TemplateExcelPtsSystemMonthly, columns);//, 1000);

            ReportFinishedEmailParameter.SendReportFinishedEmail(parameters, ref currentAction, filePathSaved,
                string.Format("PTS Monthly System Report ({0})", yearFriendlyReportName), ".xlsx");
        }

        public static string GenerateMonthlyVarianceReportYear(DateTime referenceDate, ref ICurrentAction currentAction, string networkUser,
            string networkPassword, string pathReport, string reportName, string currentDataQuery, string template, List<int> columns, int multipleValue = 1, int divideValue = 1)
        {
            currentAction.StartLog("Generating Monthly Report");

            try
            {
                currentAction.Description = "Brazil Monthly Variance PTS Report: Generation has started";

                var file = Directory.GetFiles(pathReport)
                    .Where(x => Path.GetFileNameWithoutExtension(x).StartsWith(reportName)
                        && (Path.GetExtension(x) == ".xlsx" || Path.GetExtension(x) == ".xlsb")
                        && GetDateFromFileName(x).Month == referenceDate.Month && GetDateFromFileName(x) < referenceDate)
                        .OrderByDescending(x => File.GetLastWriteTime(x)).FirstOrDefault();

                var previousDate = file != null ? GetDateFromFileName(file).Date : DateTime.MinValue;

                currentAction.Description = string.Format("Previous Date for Comparison: {0}", previousDate.ToString("dd/MM/yyyy"));

                var reportExcel = "{0}{1}_{2}.xlsx";
                var fileName = string.Format(reportExcel, reportName, referenceDate.ToString("yyyyMM"), referenceDate.ToString("yyyyMMdd"));
                var reportTempPath = Path.Combine(PtsReportSaveTempPath, fileName);

                var fileNamePrevious = string.Format(reportExcel, reportName, previousDate.ToString("yyyyMM"), previousDate.ToString("yyyyMMdd"));
                var reportPathPrevious = Path.Combine(pathReport, fileNamePrevious);

                currentAction.Description = "Reading current data from database";
                var currentData = SqlHelper.ExecuteReader(currentDataQuery, PtsConnectionString);

                var weekOfMonth = referenceDate.GetWeekOfMonth();
                var pathToSave = Path.Combine(pathReport, fileName);

                CopyReportMonthlyToLocal(referenceDate, template, previousDate, reportTempPath, reportPathPrevious);

                currentAction.Description = "Getting Monday for Reference";
                var monday = referenceDate.AddDays(-(((int)referenceDate.DayOfWeek) - 1));

                currentAction.Description = "Reading previous report or template";
                var previousData = ExcelNpoiHelper.GetData(reportTempPath, string.Format("Week {0}", weekOfMonth), true);
                var previousRows = new List<DataRow>();
                var previousDataClone = previousData.Copy();
                previousRows.AddRange(previousDataClone.Select().Where(x => x[0] != null && !string.IsNullOrWhiteSpace(x[0].ToString())).ToList());

                currentAction.Description = "Filling Columns Name";

                for (int i = startDateColumn; i < previousData.Columns.Count; i++)
                {
                    previousData.Columns[i].ColumnName = previousData.Columns[i].ColumnName
                        .Replace("Date 0", monday.AddDays(-3).ToString("dd/MM/yyyy"))
                        .Replace("Date 1", monday.ToString("dd/MM/yyyy"))
                        .Replace("Date 2", monday.AddDays(1).ToString("dd/MM/yyyy"))
                        .Replace("Date 3", monday.AddDays(2).ToString("dd/MM/yyyy"))
                        .Replace("Date 4", monday.AddDays(3).ToString("dd/MM/yyyy"))
                        .Replace("Date 5", monday.AddDays(4).ToString("dd/MM/yyyy"));

                    try
                    {
                        previousData.Columns[i].DataType = typeof(decimal);
                    }
                    catch { }
                }

                //for (int i = startDateColumn; i < 6; i++)
                //    previousData.Columns[i].ColumnName.Replace("Date", monday.AddDays(i == 0 ? -columns.Count : i - 1).ToString("dd/MM/yyyy"));

                previousData.Rows.Clear();

                currentAction.Description = "Comparing current and previous data";
                while (currentData.Rows != null && currentData.Rows.Count > 0)
                {
                    var currentRow = currentData.Rows[0];
                    var previousRow = previousRows.FirstOrDefault(x => ExistsOnMonthlyReport(x, currentRow));

                    var index = previousRows.IndexOf(previousRow);

                    if (index == -1)
                    {
                        previousRow = previousData.NewRow();
                        for (int i = 0; i < startDateColumn; i++)
                            previousRow[i] = currentRow[i];
                    }

                    for (int i = startDateColumn - 1; i < previousData.Columns.Count; i++)
                    {
                        if (string.IsNullOrWhiteSpace(previousRow[i].ToString()))
                        {
                            if (previousData.Columns[i].DataType == typeof(decimal))
                                previousRow[i] = decimal.Zero;
                            else
                                previousRow[i] = "-";
                        }
                    }

                    FillValueColumns(referenceDate, currentRow, previousRow, columns, multipleValue, divideValue);

                    if (index > -1)
                    {
                        previousData.ImportRow(previousRow);
                        previousRows.RemoveAt(index);
                    }
                    else
                        previousData.Rows.Add(previousRow);

                    currentData.Rows.RemoveAt(0);
                }

                previousRows.ForEach(x => previousData.Rows.Add(x.ItemArray));
                previousRows.RemoveAll(x => string.IsNullOrWhiteSpace(x[0].ToString()) || string.IsNullOrWhiteSpace(x[1].ToString()));

                foreach (DataRow row in previousData.Rows)
                {
                    for (int i = startDateColumn; i < previousData.Columns.Count; i++)
                    {
                        if (row[i] == null || string.IsNullOrWhiteSpace(row[i].ToString()))
                            row[i] = 0;
                    }
                }

                currentAction.Description = "Sorting report data";
                DataView dv = previousData.DefaultView;
                dv.Sort = "Program Id, Project Id asc";
                DataTable sorted = dv.ToTable();

                currentAction.Description = string.Format("Generating Temp Report: {0}", reportTempPath);
                var filePathSaved = GenerateExcelFromDataTable(sorted, string.Format("{0}{1}", reportName, referenceDate.ToString("yyyyMM")), reportTempPath,
                    ref currentAction, networkUser, networkPassword, weekOfMonth, referenceDate, true, pathReport);
                GC.Collect();

                var folderSaved = Path.GetDirectoryName(filePathSaved);
                var oldReportsFolder = Path.Combine(folderSaved, "Old");

                if (!Directory.Exists(oldReportsFolder)) Directory.CreateDirectory(oldReportsFolder);

                if (previousDate != DateTime.MinValue)
                {
                    var filesToDrop = Directory.GetFiles(oldReportsFolder).Where(x => File.GetLastWriteTime(x) <= previousDate.AddDays(-4)).ToList();

                    try
                    {
                        filesToDrop.ForEach(File.Delete);

                        currentAction.Description = "Moving Previous report to Old Folder";
                        File.Move(reportPathPrevious, Path.Combine(oldReportsFolder, Path.GetFileName(reportPathPrevious)));
                    }
                    catch { }
                }

                currentAction.Description = "Brazil Monthly Variance PTS Report Generation has finished";
                return filePathSaved;
            }
            finally
            {
                currentAction.StopLog();
            }
        }

        private static void FillValueColumns(DateTime referenceDate, DataRow currentRow, DataRow previousRow, List<int> columns, int multipleValue, int divideValue)
        {
            var startColumn = startDateColumn + (columns.Count - columns.Count / 2) * (((int)referenceDate.DayOfWeek) - 1);

            for (int i = 0; i < columns.Count; i++)
            {
                decimal value;

                if (decimal.TryParse(currentRow[columns[i]].ToString(), out value))
                {
                    if (!previousRow.Table.Columns[startColumn + i].ColumnName.Contains("%"))
                        value = value * multipleValue / divideValue;

                    previousRow[startColumn + i] = value.ToString(new System.Globalization.CultureInfo("pt-BR").NumberFormat).Replace(".", ",");
                }
                else
                    previousRow[startColumn + i] = currentRow[columns[i]];
            }
        }

        private static void CopyReportMonthlyToLocal(DateTime referenceDate, string template, DateTime previousDate, string reportTempPath, string reportPathPrevious)
        {
            if (File.Exists(reportTempPath)) File.Delete(reportTempPath);

            if (File.Exists(reportPathPrevious) && previousDate.Month == referenceDate.Month)
                File.Copy(reportPathPrevious, reportTempPath);
            else
                File.Copy(template, reportTempPath);
        }

        private static DateTime GetDateFromFileName(string file)
        {
            try
            {
                var date = Path.GetFileNameWithoutExtension(file).Split('_').Last();

                string formatString = "yyyyMMdd";
                DateTime dateParsed;

                if (DateTime.TryParseExact(date, formatString, null, System.Globalization.DateTimeStyles.None, out dateParsed))
                    return dateParsed;
                else
                    return DateTime.MinValue;
            }
            catch
            {
                return DateTime.MinValue;
            }
        }

        private static bool ExistsOnMonthlyReport(DataRow dataRow, DataRow currentRow)
        {
            for (int i = 0; i < startDateColumn - 1; i++)
            {
                if (dataRow[i].ToString() != currentRow[i].ToString())
                    return false;
            }

            return true;
        }

        private static void GenerateReportWithExcelRefresh(string reportFileName, string template, ref ICurrentAction currentAction, Dictionary<string, object> parameters,
            DateTime referenceDate, string yearFriendlyReportName, string friendlyReportName, string reportFolder = null)
        {
            try
            {
                var networkUser = parameters["RobotUsername"].ToString();
                var networkPassword = parameters["RobotPassword"].ToString();
                var fullFriendlyReportName = string.Format("{0} ({1})", friendlyReportName, yearFriendlyReportName);

                currentAction.Description = string.Format("{0}: Generation has started", yearFriendlyReportName);

                var folder = Path.Combine(PtsReportSavePath, reportFolder ?? string.Empty);

                if (!Directory.Exists(folder)) Directory.CreateDirectory(folder);

                var filePathSaved = string.Format(@"{0}\{1}_{2}{3}", folder,
                    reportFileName, referenceDate.ToString("yyyyMMdd"), Path.GetExtension(template));

                var parametersRefresh = new Dictionary<string, object>{
                   {"ExcelFile", string.Format(template, yearFriendlyReportName.Replace(" ", string.Empty)) },
                   {"RemoveDataLink", true},
                   {"TransformTableToRange", true},
                   {"PathToSaveRefreshedFile", filePathSaved}
                };

                currentAction.StartLog("Generating Report");
                new GenericApplications.SchedulerUi.Refresh().Start(referenceDate, ref currentAction, parametersRefresh);
                currentAction.StopLog();

                currentAction.Description = string.Format("{0}: Generation has finished", fullFriendlyReportName);

                ReportFinishedEmailParameter.SendReportFinishedEmail(parameters, ref currentAction, filePathSaved, fullFriendlyReportName, Path.GetExtension(template));
            }
            catch
            {
                throw;
            }
            finally
            {
                currentAction.StopLog();
            }
        }

        public static void GenerateNewLaggedReportYear(int year, ref ICurrentAction currentAction, Dictionary<string, object> parameters, DateTime referenceDate, string yearFriendlyReportName)
        {
            GenerateReportWithExcelRefresh(string.Concat(BookOfWorkLagReportName, year),
                TemplateExcelLagReport, ref currentAction, parameters, referenceDate, yearFriendlyReportName, "PTS Lagged Report", year.ToString());
        }

        public static void GenerateApplicationReportYear(int year, ref ICurrentAction currentAction, Dictionary<string, object> parameters, DateTime referenceDate, string yearFriendlyReportName)
        {
            var bztePrograms = parameters.ContainsKey("BZTEPrograms") ? bool.Parse(parameters["BZTEPrograms"].ToString()) : false;

            var reportFileName = string.Concat(bztePrograms ? BZTEApplicationReportName : ApplicationReportName, year);
            var template = bztePrograms ? TemplateExcelBZTEApplicationReport : TemplateExcelApplicationReport;
            var friendlyReportName = bztePrograms ? "[BZTE] PTS Application Lagged Report" : "PTS Application Lagged Report";
            var folderReport = bztePrograms ? string.Concat(@"Application\BZTE\", year) : string.Concat(@"Application\", year);

            GenerateReportWithExcelRefresh(reportFileName, template, ref currentAction, parameters, referenceDate, yearFriendlyReportName, friendlyReportName, folderReport);
        }

        public static string UpdateReportLagSummary(int year, ref ICurrentAction currentAction, string networkUser, string networkPassword, DateTime referenceDate)
        {
            var fileName = string.Format("BookOfWorkLAG{0}_{1}.xlsx", year, referenceDate.ToString("yyyyMMdd"));
            var reportTempPath = Path.Combine(PtsReportSaveTempPath, fileName);

            currentAction.Description = "Updating PTS Summary";
            try
            {
                ExcelHelper.RefreshAll(reportTempPath);
                currentAction.Description = "PTS Summary Updated";
            }
            catch
            {
                currentAction.Description = "PTS Summary NOT Updated";
            }

            currentAction.Description = "Copying report from Temp Folder to Report Folder";
            string filePathSaved;

            if (!Directory.Exists(PtsReportSavePath))
                Directory.CreateDirectory(PtsReportSavePath);

            filePathSaved = Path.Combine(PtsReportSavePath, fileName);
            File.Copy(reportTempPath, filePathSaved, true);
            File.Delete(reportTempPath);

            currentAction.Description = "Report copied";

            return filePathSaved;
        }

        #region Generic Report

        private static string GenerateReport(string query, string fileName, string template, ref ICurrentAction currentAction, string networkUser, string networkPassword, int sheetPosition, DateTime referenceDate, bool copyToReportFolder, string folderToSave)
        {
            currentAction.Description = "Executing query";
            var dataTable = SqlHelper.ExecuteReader(query, PtsConnectionString);
            GC.Collect();

            var pathFile = Path.Combine(PtsReportSavePath, folderToSave ?? string.Empty);
            var filePathSaved = GenerateExcelFromDataTable(dataTable, fileName, template, ref currentAction, networkUser, networkPassword,
                sheetPosition, referenceDate, copyToReportFolder, pathFile);
            GC.Collect();
            return filePathSaved;
        }

        private static string GenerateExcelFromDataTable(DataTable dataTable, string fileName, string template, ref ICurrentAction currentAction, string networkUser,
            string networkPassword, int sheetPosition, DateTime referenceDate, bool copyToReportFolder, string pathFile, int rowHeaderLength = 1)
        {
            currentAction.Description = "Generating Excel Report";
            var pathTempToSave = string.Format(@"{0}\{1}_{2}.xlsx", PtsReportSaveTempPath, fileName, referenceDate.ToString("yyyyMMdd"));
            var filePathSaved = pathTempToSave;

            using (NetworkShareAccesser.Access(template, networkUser, networkPassword))
            {
                if (!Directory.Exists(PtsReportSaveTempPath))
                    Directory.CreateDirectory(PtsReportSaveTempPath);

                if (template != pathTempToSave)
                    File.Copy(template, pathTempToSave, true);

                template = pathTempToSave;
            }

            using (var excelPackage = new ExcelPackage(new FileInfo(template), false))
            {
                var worksheet = excelPackage.Workbook.Worksheets[sheetPosition];

                for (var i = 0; i < dataTable.Columns.Count; i++)
                    worksheet.Cells[1, i + 1].Value = dataTable.Columns[i].ColumnName;

                var row = rowHeaderLength + 1;

                for (int dataRow = 0; dataRow < dataTable.Rows.Count; dataRow++)
                {
                    for (var i = 0; i < dataTable.Columns.Count; i++)
                        worksheet.Cells[row, i + 1].Value = dataTable.Rows[dataRow][i];

                    row++;
                }

                currentAction.Description = string.Format("Saving report at {0}", pathTempToSave);

                using (NetworkShareAccesser.Access(pathTempToSave, networkUser, networkPassword))
                {
                    excelPackage.Save();
                    File.Copy(excelPackage.File.FullName, pathTempToSave, true);
                }

                currentAction.Description = "Report successfully saved";
            }

            if (copyToReportFolder)
            {
                currentAction.Description = "Copying report from Temp Folder to Report Folder";

                //var pathFile = Path.Combine(PtsReportSavePath, folderToSave ?? string.Empty);

                if (!Directory.Exists(pathFile))
                    Directory.CreateDirectory(pathFile);

                var pathToSave = string.Format(@"{0}\{1}_{2}.xlsx", pathFile, fileName, referenceDate.ToString("yyyyMMdd"));

                currentAction.Description = string.Format("Copying report from temp to {0}", pathToSave);
                File.Copy(pathTempToSave, pathToSave, true);
                filePathSaved = pathToSave;
            }

            currentAction.Description = "Report copied";
            return filePathSaved;
        }

        #endregion

        public static void ScheduleReportFinishedEmail(int routineId, ReportFinishedEmailParameter parameter)
        {
            var parameters = new Dictionary<string, string>
                {
                    {"ReportFolder", parameter.Folder},
                    {"ReportExtension", parameter.Extension},
                    {"ReportPattern", parameter.Pattern},
                    {"FriendlyReportName", parameter.FriendlyName},
                    {"EmailAddressUsers", parameter.EmailAddressUsers},
                    {"EmailAddressError", parameter.EmailAddressError}
                };

            using (var client = new ScheduleClient())
                client.ExecuteRoutineOnDemandById(routineId, parameters);
        }
    }

    public class ReportFinishedEmailParameter
    {
        public static void SendReportFinishedEmail(Dictionary<string, object> parameters, ref ICurrentAction currentAction, string filePathSaved, string friendlyName, string extension)
        {
            currentAction.Description = "Scheduling Report Finished Email sent";

            var parametersEmail = new ReportFinishedEmailParameter
            {
                Folder = Path.GetDirectoryName(filePathSaved),
                FriendlyName = friendlyName,
                Pattern = Path.GetFileName(filePathSaved).Split('_').First(),
                EmailAddressError = parameters["EmailAddressError"].ToString(),
                EmailAddressUsers = parameters["EmailAddressUsers"].ToString(),
                Extension = extension
            };

            ReportGenerationHelper.ScheduleReportFinishedEmail(int.Parse(ConfigurationManager.AppSettings["ReportEmailRoutineId"]), parametersEmail);

            currentAction.Description = "Report Finished Email Scheduled. Email will be sent as soon as possible";
        }

        public string Folder { get; set; }
        public string Extension { get; set; }
        public string Pattern { get; set; }
        public string FriendlyName { get; set; }
        public string EmailAddressUsers { get; set; }
        public string EmailAddressError { get; set; }
    }
}